﻿using JobBatDeal.DbHelper;
using JobBatDeal.SqlDbHelper;
using log4net;
using MySql.Data.MySqlClient;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Z.BulkOperations;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO; // 用于处理.xlsx格式  

namespace JobBatDeal.Jobs
{
    public class DistrictCodeUptJob : IJob
    {
        private readonly ILog _logger = LogManager.GetLogger(typeof(HotelMergeJob));

        public void Execute(IJobExecutionContext context)
        {
            _logger.InfoFormat("===***********HotelBasicInfoUptJob 执行开始*********===");

            //GetGaoDeDistrictCode();
            ExportHotelBasicData();

            _logger.InfoFormat("===***********HotelBasicInfoUptJob 执行完成*********===");
        }

        public void ExportHotelBasicData()
        {
            var dtProv = DbHelperSQL.Query("SELECT Id,Name,Code FROM Provinces").Tables[0];

            if (dtProv != null && dtProv.Rows.Count > 0)
            {
                foreach (DataRow drProv in dtProv.Rows)
                {
                    _logger.InfoFormat($">>>>开始处理省份：{drProv["Name"].ToString()}");

                    var provCode = drProv["Code"].ToString();

                    var sql = $@"SELECT b.id '酒店ID',b.name '酒店名称',phone '酒店电话',address '酒店地址', b.Longitude '经度',b.Latitude '维度',c.name '城市', c.NationalStandardCode '城市国标码' FROM [HotelBasicInfoes] b
                  INNER JOIN Cities c ON b.CityId=c.id AND c.NationalStandardCode IS NOT NULL
                  WHERE b.isdeleted=0 and b.IsOnline=1 and b.CityId IN(SELECT id FROM dbo.Cities WHERE ProvinceId=(SELECT id FROM dbo.Provinces WHERE Code='{provCode}'))
                  AND EXISTS(SELECT 1 FROM HotelBestPrices_{provCode} p WHERE p.hotelid = b.id AND p.EffectiveDate >= CONVERT(VARCHAR(10), GETDATE(), 120))";

                    //var dtCity = DbHelperSQL.Query($"SELECT Id,Name FROM Cities WHERE Isdeleted =0 AND ProvinceId='{drProv["Id"].ToString()}'").Tables[0];
                    var hotelDt = DbHelperSQL.Query(sql).Tables[0];

                    ExportToExcel(hotelDt, provCode);

                    _logger.InfoFormat($">>>>省份：{drProv["Name"].ToString()} 结束>>>>");
                }
            }
        }

        public void ExportToExcel(DataTable dt, string provName)
        {
            // 指定Excel文件路径  
            string filePath = $@"d:\ToTaitanyun\{provName}.xlsx";

            if (File.Exists(filePath))
            {
                try
                {
                    // Delete the existing file
                    File.Delete(filePath);
                }
                catch (IOException ex)
                {
                    Console.WriteLine($"Error deleting existing file: {ex.Message}");
                }
            }

            // 使用NPOI导出DataTable到Excel  
            using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook(); // 创建.xlsx格式的Workbook  
                ISheet sheet = workbook.CreateSheet("Sheet1"); // 创建一个工作表  

                // 将DataTable的列标题写入Excel的第一行  
                var headerRow = sheet.CreateRow(0);
                foreach (DataColumn column in dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }

                // 遍历DataTable的所有行  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var row = sheet.CreateRow(i + 1); // Excel行索引从0开始，但数据从第二行开始写  
                    foreach (DataColumn column in dt.Columns)
                    {
                        row.CreateCell(column.Ordinal).SetCellValue(dt.Rows[i][column].ToString());
                    }
                }

                // 写入到文件  
                workbook.Write(file);
            }
        }

        public void DistrictCodeUpdateHuazhu()
        {
            var strSql = @"select HotelId,SupplierHotelId from HotelMappings where supplierid='89e38e71-47d3-4946-92f4-b41e55ecd38f' and IsDeleted=0 
                        and hotelid in
                        (select id FROM [fanjiahotelplatform].[dbo].[HotelBasicInfoes]
                          where supplierid='89e38e71-47d3-4946-92f4-b41e55ecd38f' and DistrictCode is null and IsDeleted=0)";
            var hotelMappingDt = DbHelperSQL.Query(strSql).Tables[0];

            var huazhuHotelInfoDt = DbHelperMySQL
                    .Query($@"SELECT HotelNo,CountyNo,CountyName FROM huazhuhotelinfo WHERE CountyNo IS NOT NULL AND CountyNo<>'' ", "MysqlConn", "huazhuhoteldata").Tables[0];

            if (hotelMappingDt.Rows.Count > 0 && huazhuHotelInfoDt.Rows.Count > 0)
            {
                var hotelBasicSql = "SELECT Id,DistrictCode,LastModificationTime FROM HotelBasicInfoes WHERE 1=0";
                var hotelBasicDt = DbHelperSQL.Query(hotelBasicSql).Tables[0];
                var fjDistrictNewsDt = DbHelperSQL.Query("SELECT Code,Name FROM DistrictNews").Tables[0];

                foreach (DataRow dr in hotelMappingDt.Rows)
                {
                    
                    var supplierHotelId = dr["SupplierHotelId"].ToString();

                    var selRst = huazhuHotelInfoDt.Select("HotelNo=" + supplierHotelId);
                    if (selRst != null && selRst.Length > 0)
                    {
                        var spDistrictCode = selRst[0]["CountyNo"].ToString();
                        var spDistrictName = selRst[0]["CountyName"].ToString();

                        var mFjDistrict = fjDistrictNewsDt.Select($"Code='{spDistrictCode}' AND Name='{spDistrictName}'");
                        if (mFjDistrict != null && mFjDistrict.Length > 0)
                        {
                            var drNew = hotelBasicDt.NewRow();
                            drNew["Id"] = dr["HotelId"].ToString();
                            drNew["DistrictCode"] = spDistrictCode;
                            drNew["LastModificationTime"] = DateTime.Now;

                            hotelBasicDt.Rows.Add(drNew);
                        }
                        else
                        {
                            _logger.Info($"{spDistrictCode}-{spDistrictName} 未匹配");
                        }
                    }
                }

                if (hotelBasicDt.Rows.Count > 0)
                {
                    //UptDataTableSqlServer(hotelBasicDt, "HotelBasicInfoes_DistrictCode_Huazhu");
                    UptDataTableSqlServer(hotelBasicDt, "HotelBasicInfoes");
                }
            }
        }
        
        public void DistrictCodeUpdateMeituan()
        {
            var strSql = @"select HotelId,SupplierHotelId from HotelMappings where supplierid='b8e0d4a6-ab99-444c-b1cb-75d1ea062efc' and IsDeleted=0 and hotelid in
                        (select id FROM [fanjiahotelplatform].[dbo].[HotelBasicInfoes]
                          where DistrictCode is null and IsDeleted=0)";
            var hotelMappingDt = DbHelperSQL.Query(strSql).Tables[0];

            if (hotelMappingDt.Rows.Count > 0)
            {
                DbHelperMySQL.ExecuteSqlWithConn("TRUNCATE TABLE FjHotelMapping", "MysqlConn", "meituanhoteldata");
                SaveDataTableMySql(hotelMappingDt, "FjHotelMapping", "MysqlConn", "meituanhoteldata");
            }

            var spHotelInfoDt = DbHelperMySQL
                    .Query($@"SELECT HotelId,LocationId,LocationName FROM meituanhotelinfo WHERE HotelId IN(SELECT SupplierHotelId FROM FjHotelMapping)", "MysqlConn", "meituanhoteldata").Tables[0];

            if (hotelMappingDt.Rows.Count > 0 && spHotelInfoDt.Rows.Count > 0)
            {
                var hotelBasicSql = "SELECT Id,DistrictCode,LastModificationTime FROM HotelBasicInfoes WHERE 1=0";
                var hotelBasicDt = DbHelperSQL.Query(hotelBasicSql).Tables[0];
                var fjDistrictNewsDt = DbHelperSQL.Query("SELECT * FROM DistrictsMeituanMapping").Tables[0];

                foreach (DataRow dr in hotelMappingDt.Rows)
                {
                    var supplierHotelId = dr["SupplierHotelId"].ToString();

                    var selRst = spHotelInfoDt.Select("HotelId=" + supplierHotelId);
                    if (selRst != null && selRst.Length > 0)
                    {
                        var spDistrictCode = selRst[0]["LocationId"].ToString();
                        var spDistrictName = selRst[0]["LocationName"].ToString();

                        var mFjDistrict = fjDistrictNewsDt.Select($"MeiTuanCode='{spDistrictCode}'");
                        if (mFjDistrict != null && mFjDistrict.Length > 0)
                        {
                            var drNew = hotelBasicDt.NewRow();
                            drNew["Id"] = dr["HotelId"].ToString();
                            drNew["DistrictCode"] = mFjDistrict[0]["FjCode"];
                            drNew["LastModificationTime"] = DateTime.Now;

                            hotelBasicDt.Rows.Add(drNew);
                        }
                        else
                        {
                            _logger.Info($"{spDistrictCode}-{spDistrictName} 未匹配");
                        }
                    }
                }

                if (hotelBasicDt.Rows.Count > 0)
                {
                    //UptDataTableSqlServer(hotelBasicDt, "HotelBasicInfoes_DistrictCode_Huazhu");
                    UptDataTableSqlServer(hotelBasicDt, "HotelBasicInfoes");
                }
            }
        }

        public void GetGaoDeDistrictCode()
        {
            var dtDistrictFj = DbHelperSQL.Query("SELECT FjCode,FjName,MeituanCityName CityName FROM DistrictsMeituanMapping").Tables[0];
            //var dtDistrictFj = DbHelperSQL.Query("SELECT d.[Code] FjCode,d.[Name] FjName,c.name CityName FROM DistrictsNew d,cities c WHERE d.cityid=c.id").Tables[0];
            BrushingDistrictCode(dtDistrictFj);
        }

        /// <summary>
        /// 刷酒店基础信息表的区域编码
        /// </summary>
        private int BrushingDistrictCode(DataTable hotelDt)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            {
                new DataColumn("FjCode",typeof(string)),
                new DataColumn("FjName",typeof(string)),
                new DataColumn("DistrictCode",typeof(string)),
                new DataColumn("DistrictName",typeof(string))
            });
            foreach (DataRow dr in hotelDt.Rows)
            {
                string adress = $"{dr["CityName"]}{dr["FjName"]}";
                string cityName = dr["CityName"].ToString();
                string id = dr["FjCode"].ToString();
                var (dbcode, district) = GetGaoDeAdCode(adress, cityName);
                DataRow d = dt.NewRow();
                d[0] = id;
                d[1] = dr["FjName"].ToString();
                d[2] = dbcode;
                d[3] = district;
                dt.Rows.Add(d);
            }

            //SyncDistrictCode(dt);
            SaveDataTableSqlServer(dt, "Districts_Gaode", "FjCode");

            return dt.Rows.Count;
        }

        /// <summary>
        /// 根据地址获取高德区域编码
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        private (string, string) GetGaoDeAdCode(string address, string cityName="")
        {
            var gaodeResult = RequestGaoDeApiSingle(); //调用高德地图接口获取区域code
            if (gaodeResult is null) return ("0", "");
            else
            {
                try
                {
                    var geoModel = WindowsFormsApplication1.Common.GeoModel.FromJson(gaodeResult);
                    if (geoModel == null || geoModel.Geocodes == null || geoModel.Geocodes.Length == 0)
                    {
                        _logger.InfoFormat($"（{nameof(BrushingDistrictCode)}）酒店地址【{address}】未刷到数据");
                        return ("-1", "");
                    }
                    return (geoModel.Geocodes[0].Adcode.ToString(), geoModel.Geocodes[0].District.ToString());
                }
                catch (Exception ex)
                {
                    _logger.InfoFormat($"（{nameof(BrushingDistrictCode)}）刷酒店地址【{address}】区域编码获取出错：{ex.Message}");
                    return ("0", "");
                }
            }
            string RequestGaoDeApiSingle()
            {
                string apiUrl = "http://restapi.amap.com/v3/geocode/geo";
                string apiKey = "08d219e2e53e34db81003e1f7d3daf7c";// "8bc8d2dd7ee8ca44d0ee055b27ff34f4","560fa087445311bd9b55beb3202c7533";
                string output = "json";

                IDictionary<string, string> param = new Dictionary<string, string>();
                param.Add("key", apiKey);
                param.Add("output", output);

                if (!string.IsNullOrEmpty(address))
                    param.Add("address", address);
                if (!string.IsNullOrEmpty(cityName))
                    param.Add("city", cityName);
                string result = string.Empty;
                try
                {
                    //以 Get 形式请求 Api 地址
                    result = WindowsFormsApplication1.Common.HttpUtils.DoGet(apiUrl, param);
                }
                catch (Exception ex)
                {
                    _logger.InfoFormat($"===***********{address}获取出错：{ex.Message}");
                    result = null;
                }
                return result;
            }
        }

        private void UptDataTableSqlServer(DataTable dtSave, string tableName, string keyName = "Id")
        {
            try
            {
                var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                using (DbConnection connection = new SqlConnection(connectionString))
                {
                    dtSave.TableName = tableName; //"HotelBasicInfoes_DistrictCode_Huazhu";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = keyName;
                    bulk.AllowDuplicateKeys = true;
                    //bulk.BulkInsert(dtSave);
                    bulk.BulkUpdate(dtSave);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dtSave);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private void SaveDataTableSqlServer(DataTable dtSave, string tableName, string keyName = "Id")
        {
            try
            {
                var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                using (DbConnection connection = new SqlConnection(connectionString))
                {
                    dtSave.TableName = tableName; //"HotelBasicInfoes_DistrictCode_Huazhu";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = keyName;
                    bulk.BulkInsert(dtSave);
                    //bulk.BulkUpdate(dtSave);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dtSave);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        public void SaveDataTableMySql(DataTable dtSave, string tableName, string connStr, string dbStr)
        {
            var mysqlConnStr = DbHelperMySQL.GetConnStr(connStr, dbStr);

            try
            {
                using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                {
                    dtSave.TableName = tableName;
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "HotelId";
                    bulk.BulkInsert(dtSave);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception xe)
            {
                _logger.Error(xe.Message, xe);
            }
        }

    }
}
